"""
商品数据模型
"""
from sqlalchemy import Column, Integer, String, Text, Float, Boolean, JSON, Index, DateTime, ForeignKey
from sqlalchemy.dialects.postgresql import ARRAY
from datetime import datetime
from app.models.base import BaseModel


class Product(BaseModel):
    """商品模型"""
    
    __tablename__ = "products"
    
    # 基本信息
    product_sn = Column(String(50), unique=True, nullable=False, index=True, comment="商品编号")
    name = Column(String(200), nullable=False, index=True, comment="商品名称")
    brand = Column(String(100), nullable=True, index=True, comment="品牌")
    category = Column(String(100), nullable=False, index=True, comment="商品分类")
    sub_category = Column(String(100), nullable=True, comment="子分类")
    
    # 价格信息
    price = Column(Float, nullable=False, index=True, comment="售价")
    original_price = Column(Float, nullable=True, comment="原价")
    cost_price = Column(Float, nullable=True, comment="成本价")
    
    # 库存信息
    stock = Column(Integer, default=0, nullable=False, comment="库存数量")
    sales_count = Column(Integer, default=0, nullable=False, comment="销量")
    
    # 商品描述
    description = Column(Text, nullable=True, comment="商品描述")
    specifications = Column(JSON, nullable=True, comment="规格参数（JSON格式）")
    features = Column(ARRAY(String), nullable=True, comment="产品特点")
    
    # 图片
    main_image = Column(String(500), nullable=True, comment="主图URL")
    images = Column(ARRAY(String), nullable=True, comment="商品图片列表")
    
    # 评价信息
    rating = Column(Float, default=5.0, nullable=False, comment="评分（1-5）")
    review_count = Column(Integer, default=0, nullable=False, comment="评价数量")
    
    # 适用信息
    suitable_for = Column(ARRAY(String), nullable=True, comment="适用人群/场景")
    tags = Column(ARRAY(String), nullable=True, comment="标签")
    
    # 关键指标（用于推荐算法）
    hot_score = Column(Float, default=0.0, nullable=False, index=True, comment="热度分数")
    quality_score = Column(Float, default=0.0, nullable=False, comment="品质分数")
    cost_performance = Column(Float, default=0.0, nullable=False, comment="性价比分数")
    
    # 状态
    is_active = Column(Boolean, default=True, nullable=False, comment="是否上架")
    is_recommended = Column(Boolean, default=False, nullable=False, comment="是否推荐")
    
    # 索引
    __table_args__ = (
        Index('idx_category_price', 'category', 'price'),
        Index('idx_brand_category', 'brand', 'category'),
        Index('idx_hot_score', 'hot_score'),
    )
    
    def __repr__(self):
        return f"<Product(sn={self.product_sn}, name={self.name}, price={self.price})>"


class UserPreference(BaseModel):
    """用户偏好模型"""
    
    __tablename__ = "user_preferences"
    
    user_id = Column(Integer, nullable=False, unique=True, index=True, comment="用户ID")
    
    # 价格偏好
    avg_order_amount = Column(Float, default=0.0, comment="平均订单金额")
    price_sensitivity = Column(String(20), default="medium", comment="价格敏感度: low/medium/high")
    
    # 品牌偏好
    preferred_brands = Column(ARRAY(String), nullable=True, comment="偏好品牌列表")
    
    # 品类偏好
    preferred_categories = Column(ARRAY(String), nullable=True, comment="偏好品类列表")
    last_viewed_category = Column(String(100), nullable=True, comment="最近浏览品类")
    
    # 购买习惯
    total_orders = Column(Integer, default=0, comment="总订单数")
    favorite_products = Column(ARRAY(String), nullable=True, comment="收藏商品ID列表")
    
    # 导购历史
    shopping_guide_history = Column(JSON, nullable=True, comment="导购历史记录")
    
    def __repr__(self):
        return f"<UserPreference(user_id={self.user_id})>"


class ProductReview(BaseModel):
    """商品评价模型（可选，用于更丰富的推荐）"""
    
    __tablename__ = "product_reviews"
    
    product_id = Column(Integer, nullable=False, index=True, comment="商品ID")
    user_id = Column(Integer, nullable=False, index=True, comment="用户ID")
    order_sn = Column(String(50), nullable=True, comment="订单号")
    
    # 评价内容
    rating = Column(Integer, nullable=False, comment="评分（1-5）")
    content = Column(Text, nullable=True, comment="评价内容")
    
    # 评价维度
    quality_rating = Column(Integer, nullable=True, comment="质量评分")
    service_rating = Column(Integer, nullable=True, comment="服务评分")
    logistics_rating = Column(Integer, nullable=True, comment="物流评分")
    
    # 图片
    images = Column(ARRAY(String), nullable=True, comment="评价图片")
    
    # 状态
    is_visible = Column(Boolean, default=True, comment="是否显示")
    helpful_count = Column(Integer, default=0, comment="有帮助数")
    
    def __repr__(self):
        return f"<ProductReview(product_id={self.product_id}, rating={self.rating})>"


class ProductPriceHistory(BaseModel):
    """商品价格历史模型"""
    
    __tablename__ = "product_price_history"
    
    product_id = Column(Integer, ForeignKey('products.id'), nullable=False, index=True, comment="商品ID")
    product_sn = Column(String(50), nullable=False, index=True, comment="商品编号")
    price = Column(Float, nullable=False, comment="售价")
    original_price = Column(Float, nullable=True, comment="原价")
    recorded_at = Column(DateTime, default=datetime.utcnow, nullable=False, index=True, comment="记录时间")
    change_reason = Column(String(100), nullable=True, comment="价格变动原因")
    
    # 索引
    __table_args__ = (
        Index('idx_product_time', 'product_id', 'recorded_at'),
        Index('idx_product_sn_time', 'product_sn', 'recorded_at'),
    )
    
    def __repr__(self):
        return f"<ProductPriceHistory(product_sn={self.product_sn}, price={self.price}, date={self.recorded_at})>"

